package BookDB;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import BookReturn.BookRecordBean;
import Loginout.UserBean;
import Loginout.MD5;

public class h2DAO {
	@SuppressWarnings("unused")
	private static final long serialVersionUID = 2L;

	public static Timestamp getNowTime() throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			Timestamp nowtimes = null;
			PreparedStatement timeps = con
					.prepareStatement("select current_timestamp");
			ResultSet rstime = timeps.executeQuery();
			if (rstime.next()) {
				nowtimes = rstime.getTimestamp(1);
			}
			return nowtimes;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}
	}

	public static Timestamp getReturnTime() throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			Timestamp retimes = null;
			PreparedStatement timeps = con
					.prepareStatement("select current_timestamp+14");
			ResultSet rstime = timeps.executeQuery();
			if (rstime.next()) {
				retimes = rstime.getTimestamp(1);
			}
			return retimes;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}
	}

	public static BookBean SelectBook(String bookid) throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			BookBean BB = null;
			PreparedStatement ps = con
					.prepareStatement("SELECT * FROM tblBooks where bookid=?");
			ps.setString(1, bookid);
			ResultSet rs = ps.executeQuery();

			if (rs.next())
				BB = new BookBean(rs.getString("bookID"), rs.getString("ISBN"),
						rs.getString("BOOKNAME"), rs.getString("CALID"),
						rs.getBoolean("Status"), rs.getString("location"));
			else
				BB = new BookBean("", "", "", "", false, "");

			return BB;

		} catch (Exception e) {
			e.printStackTrace();
			return new BookBean("", "", "", "", false, "");
		} finally {
			con.close();
		}
	}

	public static BookRecordBean SelectBookRecord(String bookid)
			throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			BookRecordBean RR = null;
			PreparedStatement ps = con
					.prepareStatement("SELECT * FROM tblRecords where bookid=? and ReturnDate is null");
			ps.setString(1, bookid);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				RR = new BookRecordBean(rs.getInt("RecID"),
						rs.getString("bookid"), rs.getString("ISBN"),
						rs.getString("UserID"), rs.getTimestamp("BorrowDate"),
						rs.getTimestamp("ReturnDate"),
						rs.getTimestamp("ExpiryDate"));
			} else
				RR = new BookRecordBean();

			return RR;

		} catch (Exception e) {
			e.printStackTrace();
			return new BookRecordBean();
		} finally {
			con.close();
		}
	}

	public static List<UserBean> SelectReminderList() throws SQLException {
		List<UserBean> rrlist = new ArrayList<UserBean>();
		Connection con = h2DB.getConnection();
		try {

			UserBean RR = null;
			PreparedStatement ps = con
					.prepareStatement("Select userid, email from tblusers where email is not null and userid in (SELECT userid FROM tblRecords where ReturnDate is null and ExpiryDate>=current_timestamp)");

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				RR = new UserBean();
				RR.setUserid(rs.getString("UserID"));
				RR.setEmail(rs.getString("Email"));
				rrlist.add(RR);
			}

			return rrlist;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}
	}

	public static UserBean SelectBorrowUser(String userid) throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			UserBean CC = null;
			PreparedStatement ps = con
					.prepareStatement("SELECT * FROM tblUsers where userid=?");
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				CC = new UserBean(rs.getString("UserID"),
						rs.getString("UserName"), rs.getInt("BorrowingBook"));
			} else
				CC = new UserBean();

			return CC;

		} catch (Exception e) {
			e.printStackTrace();
			return new UserBean();
		} finally {
			con.close();
		}
	}

	public static int updatebook4return(String bookid, String userid)
			throws SQLException {
		Connection con = h2DB.getConnection();
		try {

			PreparedStatement returnps = con
					.prepareStatement("update tblbooks set status=? where bookid=?");
			returnps.setBoolean(1, false);
			returnps.setString(2, bookid);
			int count1 = returnps.executeUpdate();

			int count2 = 0;
			if (count1 == 1) {
				PreparedStatement psbookreturn = con
						.prepareStatement("update tblrecords set returndate=? where bookid=? and returndate is ?");
				psbookreturn.setTimestamp(1, getNowTime());
				psbookreturn.setString(2, bookid);
				psbookreturn.setTimestamp(3, null);
				count2 = psbookreturn.executeUpdate();
			}

			int count3 = 0;
			if (count2 == 1) {
				PreparedStatement preUserReturn = con
						.prepareStatement("select * from tblUsers where userid=?");
				preUserReturn.setString(1, userid);
				ResultSet rsuser = preUserReturn.executeQuery();
				int borrowing = 0;
				if (rsuser.next())
					borrowing = rsuser.getInt("borrowingbook");
				PreparedStatement UserReturn = con
						.prepareStatement("update tblUsers set borrowingbook=? where userid=?");
				UserReturn.setInt(1, (borrowing - 1));
				UserReturn.setString(2, userid);
				count3 = UserReturn.executeUpdate();
			}

			if (count3 == 1) {
				con.commit();
			}
			return count3;

		} catch (Exception e) {

			e.printStackTrace();
			con.rollback();
			return 0;

		} finally {
			con.close();
		}

	}

	public static int updatebook4borrow(String bookid, String userid,
			String isbn) throws SQLException {
		Connection con = h2DB.getConnection();

		try {

			PreparedStatement psuse = con
					.prepareStatement("select top 1 recid from tblRecords order by recid desc");
			ResultSet rs = psuse.executeQuery();
			int recid = 0;

			if (rs.next()) {
				recid = rs.getInt("recid");
			}

			PreparedStatement psbook = con
					.prepareStatement("update tblbooks set status=? where bookid=?");
			psbook.setBoolean(1, true);
			psbook.setString(2, bookid);
			int count1 = psbook.executeUpdate();

			Timestamp nowtimes = null;
			PreparedStatement timeps = con
					.prepareStatement("select current_timestamp");

			ResultSet rstime = timeps.executeQuery();

			if (rstime.next()) {
				nowtimes = rstime.getTimestamp(1);
			}
			Timestamp exptime = null;
			PreparedStatement exptimeps = con
					.prepareStatement("select current_timestamp+7");
			ResultSet rsexptime = exptimeps.executeQuery();

			if (rsexptime.next()) {
				exptime = rsexptime.getTimestamp(1);
			}

			int count2 = 0;

			if (count1 == 1) {
				PreparedStatement psrec = con
						.prepareStatement("insert into tblRecords values (?,?,?,?,?,?,?)");

				psrec.setInt(1, recid + 1);
				psrec.setString(2, bookid);
				psrec.setString(3, isbn);
				psrec.setString(4, userid);
				psrec.setTimestamp(5, nowtimes);
				psrec.setTimestamp(6, null);
				psrec.setTimestamp(7, exptime);

				count2 = psrec.executeUpdate();

			}

			int count3 = 0;
			if (count2 == 1) {
				PreparedStatement prepsuser = con
						.prepareStatement("SELECT BORROWINGBOOK FROM TBLusers where userid=?");
				prepsuser.setString(1, userid);

				ResultSet rs2 = prepsuser.executeQuery();
				int bbook = 0;

				if (rs2.next())
					bbook = rs2.getInt("BORROWINGBOOK");

				PreparedStatement psuser = con
						.prepareStatement("update tblUsers set borrowingbook=? where userid=?");
				psuser.setInt(1, (bbook + 1));
				psuser.setString(2, userid);
				count3 = psuser.executeUpdate();

			}
			if (count3 == 1) {
				con.commit();
			}
			return count3;

		} catch (Exception e) {

			e.printStackTrace();
			con.rollback();
			return 0;

		} finally {
			con.close();
		}

	}
	
	public static List<String> getAllBookLoction() throws SQLException
	{
		Connection con = h2DB.getConnection();
	try {
		List<String> list=new ArrayList<String>();
		String l = null;
		PreparedStatement ps = con
				.prepareStatement("select location FROM tblBooks order by location" );
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			l = rs.getString("location");
			list.add(l);
		}
		return list;

	} catch (Exception e) {
		e.printStackTrace();
		return new ArrayList<String>();
	} finally {
		con.close();
	}
}
	
	public static UserBean SelectUser(String useid) throws SQLException {
		Connection con = h2DB.getConnection();

		try {
			UserBean UB = null;
			PreparedStatement ps = con
					.prepareStatement("SELECT * FROM tblUsers where userid=?");
			ps.setString(1, useid);
			ResultSet rs = ps.executeQuery();

			if (rs.next())
				UB = new UserBean(rs.getString("UserID"),
						rs.getString("Password"), rs.getString("UserName"),
						rs.getInt("borrowingbook"), rs.getString("roleID"));

			else
				UB = new UserBean("", "", "", 0, "");

			return UB;

		} catch (Exception e) {
			e.printStackTrace();
			return new UserBean("", "", "", 0, "");
		} finally {
			con.close();
		}
	}

	public static List<BookBean> SelectBookList(String keyword)
			throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			BookBean BB = null;
			List<BookBean> Blist = new ArrayList<BookBean>();
			PreparedStatement ps = con
					.prepareStatement("SELECT * FROM tblBooks where bookname like ? order by bookid");
			ps.setString(1, "%" + keyword + "%");
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				BB = new BookBean(rs.getString("booKid"), rs.getString("ISBN"),
						rs.getString("BOOKNAME"), rs.getString("CALID"),
						rs.getBoolean("Status"), rs.getString("location"));
				Blist.add(BB);
			}

			return Blist;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}
	}

	public static int adduser(UserBean user) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = h2DB.getConnection();
		try {

			PreparedStatement ps = con
					.prepareStatement("insert into tblUsers values (?,?,?,?,?,?,?)");
			ps.setString(1, user.getUserid());
			ps.setString(2, user.getUsername());
			ps.setString(3, MD5.getMD5(user.getPasword()));
			ps.setString(4, user.getRoleid());
			ps.setString(5, user.getRolename());
			ps.setInt(6, 0);
			ps.setString(7, user.getEmail());

			int ok = ps.executeUpdate();
			con.commit();
			return ok;

		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
			return 0;
		} finally {
			con.close();
		}
	}

	public static List<BookRecordBean> SelectReocrdlist(String keyword)
			throws SQLException {
		Connection con = h2DB.getConnection();

		try {
			BookRecordBean RR = null;
			List<BookRecordBean> rrlist = new ArrayList<BookRecordBean>();
			PreparedStatement ps = con
					.prepareStatement("select expirydate from tblRecords where bookid in ( select bookid from tblBooks where bookname like ?) and returndate ?");
			ps.setString(1, "%" + keyword + "%");
			ps.setString(2, "is null");
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				RR = new BookRecordBean();
				RR.setReturnDate(rs.getTimestamp("ReturnDate"));
				rrlist.add(RR);
			}

			return rrlist;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}
	}

	public static List<BookBean> SelectBookList(String bookid, String isbn,
			String bookname, String calid, String borrow, String location)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection con = h2DB.getConnection();
		try {
			BookBean BB = null;
			List<BookBean> Blist = new ArrayList<BookBean>();

			String sql = "select * from tblBooks where bookname like ? ";

			int varNum = 1;
			if (bookid != "" && bookid!=null) {
				sql = sql + "and bookid=?";
				varNum++;
			}
			if (isbn != "" && isbn!=null) {
				sql = sql + "and isbn=? ";
				varNum++;
			}
			if (calid != "" && calid!=null) {
				sql = sql + "and calid=? ";
				varNum++;
			}
			if (borrow != "" && borrow!=null) {
				sql = sql + "and status=? ";
				varNum++;
			}
			if (location != "" && location!=null) {
				sql = sql + "and location=?";
				varNum++;
			}
			PreparedStatement ps = con.prepareStatement(sql);
			if (location != "" && location!=null) {
				ps.setString(varNum, location);
				varNum--;
			}
			if (borrow != "" && borrow!=null) {
				ps.setString(varNum, borrow);
				varNum--;
			}
			if (calid != "" && calid!=null) {
				ps.setString(varNum, calid);
				varNum--;
			}
			if (isbn != "" && isbn!=null) {
				ps.setString(varNum, isbn);
				varNum--;
			}
			if (bookid != "" && bookid!=null) {
				ps.setString(varNum, bookid);
				varNum--;
			}			
			ps.setString(1, "%" + bookname + "%");
			ResultSet rs = ps.executeQuery();            		
			while (rs.next()) {
				BB = new BookBean(rs.getString("bookid"), rs.getString("ISBN"),
						rs.getString("bookname"), rs.getString("calid"),
						rs.getBoolean("Status"), rs.getString("location"));
				Blist.add(BB);
			}
			return Blist;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			con.close();
		}

	}

	public static String getTableID() throws SQLException {
		Timestamp ndt = getNowTime();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmmssSSS");
		return sf.format(ndt);
	}

	
	public static String getCalidDesp(String calid) throws SQLException
	{
		Connection con = h2DB.getConnection();
		try {
	
			String calidDesp=null;
			PreparedStatement ps = con
					.prepareStatement("select caldescription from tblCalnums where calid=?");
			ps.setString(1, calid);
			
			ResultSet rs=ps.executeQuery();
			if (rs.next())
			calidDesp=rs.getString("caldescription");
			return calidDesp;

		} catch (Exception e) {
			e.printStackTrace();
			return "";
		} finally {
			con.close();
		}
	}
		
	}

